/*
 * RepairDAO.java
 *
 * Created on March 22, 2007, 6:38 PM
 */

package Data;

import java.sql.*;
import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Dallin Regehr
 */
public class RepairDAO extends RevenueSourceDAO{
    
    private static RepairDAO instance = null;
    
    /** Creates a new instance of RSSFeedDAO */
    private RepairDAO() {
    }
    
    public static synchronized RepairDAO getInstance() {
        if (instance == null) {
            instance = new RepairDAO();
        }
        return instance;
    }
    
    
    /////////////////////////////////
    ///   CREATE
    
    public RepairBO create() throws Exception {
        String id = GUID.generate();
        RepairBO repair = new RepairBO(id);
        Cache c = Cache.getInstance();
        c.put(repair.getId(), repair);
        return repair;
    }
    
    public RepairBO create(String id){
        RepairBO Repair = new RepairBO(id);
        Cache c = Cache.getInstance();
        c.put(Repair.getId(),Repair);
        return Repair;
    }
    
    
    /////////////////////////////////////
    ///   READ
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     */
    public synchronized RevenueSourceBO read(String id) throws DataException {
        RevenueSourceBO repair;
        Connection conn;
        // check to see if id in the cache
        // if so, return it immediately
        if(Cache.getInstance().containsKey(id)){
            return (RevenueSourceBO) Cache.getInstance().get(id);
        }
        try {
            // retrieve a database connection from the pool
            conn = ConnectionPool.getInstance().get();
            // call read with a connection (the other read method in this class)
            repair = read(id,conn);
            // release the connection
            ConnectionPool.getInstance().release(conn);
            // rollback
            conn.rollback();
        }catch (ConnectionPoolException ex) {
            throw new DataException("Unable to communicate with the Database", ex);
            
        }catch (SQLException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        }
        return repair;
    }
    /**
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     */
    public RevenueSourceBO read(String id,Connection conn) throws DataException {
        RepairBO repair = null;
        // check the cache to see if the customer is in the cache
        // if so, return it immediately
        if(Cache.getInstance().containsKey(id)){
            return (RevenueSourceBO) Cache.getInstance().get(id);
        }
        
        try{
            
            repair = create(id);
            Cache.getInstance().put(repair.getId(),repair);
            super.read(id,conn);
            
            PreparedStatement pstmt =conn.prepareStatement("Select * from \"Repair\" WHERE id=?");
            pstmt.setString(1,id);
            
            ResultSet rs = pstmt.executeQuery();
            // get a rs from an sql SELECT statement
            // if( rs.next()){convert the rs to the Repair}
            if(rs.next()){
                repair.setStartDate(rs.getDate("StartDate"));
                repair.setCompletedDate(rs.getDate("CompletedDate"));
                repair.setPickedUpDate(rs.getDate("PickedUpDate"));
                repair.setRepairDescription(rs.getString("Description"));
                repair.setLaborHours(rs.getDouble("LaborHours"));
                repair.setAmount(rs.getDouble("Amount"));
                EmployeeBO emp = EmployeeDAO.getInstance().read(rs.getString("EmployeeID"));
                repair.setEmployee(emp);
                
                //save to the cache
                repair.setAlreadyInDB(true);
                Cache.getInstance().put(repair.getId(),repair);
                
                // else {handling for no customer with that GUID
            }
        }catch(SQLException e){
            //rollback
            throw new DataException("Could not retrieve record for id=" + id,e);
        }
        //release the connection back to the pool.
        return repair.getRevenueSource();
    }
    
    //////////////////////////////////
    ///   UPDATE
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     */
    public synchronized void save(RepairBO Repair) throws DataException {
        
        try {
            // retrieve a database connection from the pool
            Connection conn = ConnectionPool.getInstance().get();
            // call save with a connection (the other save method in this class)
            save(Repair, conn);
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }catch (SQLException e) {
            // rollback
            throw new DataException("Could not save record for id = " + Repair.getId(), e);
        }catch (ConnectionPoolException e){
            throw new DataException("Could not retrieve connection", e);
        }
        
        // release the connection back to the pool
        
    }
    
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the CustomerDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     */
    synchronized void save(RepairBO repair, Connection conn) throws SQLException, DataException {
        // check the dirty flag in the object.  if it is dirty,
        // run update or insert
        if (repair.isDirty()) {
            if (repair.isAlreadyInDB()) {
                update(repair, conn);
            }else{
                insert(repair, conn);
            }
            // set the dirty flag to false now that we've saved it
            repair.setDirty(false);
        }
        
        // call save(bo, conn) on any subobjects (like CustomerDAO to RepairshipDAO)
        /*This next line was removed because we don't have all of the code to remediate the circulare saves.
         CustomerDAO.getInstance().save(Repair.getCustomer(),conn);
         **/
        // touch the cache for the object
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void update(RepairBO repair,Connection conn) throws SQLException, DataException {
        super.save(repair.getRevenueSource());
        // do the update statement
        PreparedStatement pstmt =conn.prepareStatement("UPDATE \"Repair\" SET \"StartDate\" = ?," +
                " \"CompletedDate\" = ?," +
                " \"PickedUpDate\" = ?," +
                " \"Description\" = ?" +
                " \"LaborHours\" = ?" +
                " \"Amount\" = ?" +
                " \"EmployeeID\" = ?" +
                " WHERE \"ID\" = ?");
        pstmt.setDate(1, (Date) repair.getStartDate());
        pstmt.setDate(2, (Date) repair.getCompletedDate());
        pstmt.setDate(3, (Date) repair.getPickedUpDate());
        pstmt.setString(4,repair.getRepairDescription());
        pstmt.setDouble(5,repair.getLaborHours());
        pstmt.setDouble(6,repair.getAmount());
        pstmt.setString(7,repair.getEmployee().getId());
        pstmt.setString(8,repair.getId());
        
        pstmt.executeUpdate();
        
        System.out.println("RepairUpdateSuccessful");
    }
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void insert(RepairBO repair,Connection conn) throws SQLException, DataException {
        super.save(repair.getRevenueSource());
        // do the insert SQL statement
        PreparedStatement pstmt =conn.prepareStatement("INSERT INTO \"Repair\" (ID, \"StartDate\", \"CompletedDate\", \"PickedUpDate\" "+
                " \"Description\", \"LaborHours\", \"Amount\", \"EmployeeID\") VALUES (?,?,?,?,?,?,?,?)");
        
        pstmt.setString(1, repair.getId());
        pstmt.setDate(2, (Date) repair.getStartDate());
        pstmt.setDate(3, (Date) repair.getCompletedDate());
        pstmt.setDate(4, (Date) repair.getPickedUpDate());
        pstmt.setString(5,repair.getRepairDescription());
        pstmt.setDouble(6,repair.getLaborHours());
        pstmt.setDouble(7,repair.getAmount());
        pstmt.setString(8,repair.getEmployee().getId());
        
        pstmt.executeUpdate();
        System.out.println("RepairInsertSucessful");
        
        // tell the object that it's now in the db (so we call update next time not insert)
        repair.setAlreadyInDB(true);
    }
    
    ////////////////////////////////////
    ///   DELETE
    
    // We have decided not to support delete because we would like the Repairs to stay in the database for data archiving.
    
    //////////////////////////////
    ///  SEARCH methods
    
    public List<String[]> getAll() throws SQLException,DataException {
        // get the connection
        Connection conn=null;
        try {
            conn=ConnectionPool.getInstance().get();
        } catch (ConnectionPoolException e){
            throw new DataException("Could not retrieve Connection", e);
        }
        List<String[]> RepairList = new ArrayList<String[]>();
        
        // sql the names, phone, and ids
        PreparedStatement pstmt =conn.prepareStatement("Select * from \"Repair\"");
        ResultSet rs = null;
        rs = pstmt.executeQuery();
        // get a rs from an sql SELECT statement
        if(rs.next()){
            do{
                String[] temp= new String[4];
                temp[0]= rs.getString("ID");
                temp[0]= rs.getString("CreditCard");
                temp[0]= rs.getString("StartDate");
                temp[0]= rs.getString("ExpirationDate");
                RepairList.add(temp);
                
            }while(rs.next());
            
        }else{
            throw new DataException("List was unable to be retrieved.");
        }
        
        
        // while loop to populate the list from the results
        
        // release my connection
        
        // return the list
        return RepairList;
    }
    public List<RepairBO> getAllNotPickedup() throws DataException {
        List<RepairBO> RepairList=null;
        Connection conn=null;
        try {
            conn=ConnectionPool.getInstance().get();
            
            RepairList = new ArrayList<RepairBO>();
            PreparedStatement pstmt =conn.prepareStatement("Select * from \"Repair\" Where \"PickedUpDate\" IS NULL");
            ResultSet rs = null;
            rs = pstmt.executeQuery();
            
            
            if(rs.next()){
                do{
                    System.out.println("Repair found!");
                    RepairBO repair = create(rs.getString("ID"));
                    repair.setStartDate(rs.getDate("StartDate"));
                    repair.setCompletedDate(rs.getDate("CompletedDate"));
                    repair.setRepairDescription(rs.getString("Description"));
                    repair.setLaborHours(rs.getDouble("LaborHours"));
                    repair.setAmount(rs.getDouble("Amount"));
                    EmployeeBO emp = EmployeeDAO.getInstance().read(rs.getString("EmployeeID"));
                    repair.setEmployee(emp);
                    RepairList.add(repair);
                }while(rs.next());
                
            }else{
                throw new DataException("List was unable to be retrieved.");
            }
        } catch (ConnectionPoolException e){
            throw new DataException("Could not retrieve Connection", e);
        } catch(SQLException ex){
            throw new DataException("Error reading from database.",ex);
        }
        return RepairList;
    }
}